package com.netcracker.util.dao.impl;

import com.netcracker.db.exception.DBException;
import com.netcracker.db.DBManager;
import com.netcracker.entity.Country;
import com.netcracker.util.dao.CountryDAO;
import com.netcracker.util.dao.DAOFacade;
import com.netcracker.util.dao.closer.DAOCloseHelper;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class CountryDAOImpl extends DAOCloseHelper implements CountryDAO {
    protected static final String COUNTRY_ID_VALUE = "CountryId";
    protected static final String COUNTRY_NAME_VALUE = "CountryName";

    protected static final String FIND_ALL_COUNTRY_SQL = "SELECT CountryId, CountryName FROM COUNTRIES";
    protected static final String FIND_COUNTRY_BY_ID_SQL = "SELECT CountryId, CountryName FROM COUNTRIES WHERE CountryId = ?";
    protected static final String FIND_COUNTRY_COUNT_SQL = "SELECT COUNT(*) FROM COUNTRIES";

    private DBManager dbManager;
    private DAOFacade daoFacade;
    
    public CountryDAOImpl(DBManager dbManager, DAOFacade daoFacade) {
        this.dbManager = dbManager;
        this.daoFacade = daoFacade;
    }
        
    @Override
    public void setDAOFacade(DAOFacade daoFacade) {
        this.daoFacade = daoFacade;
    }
    
    @Override
    public void setDBManager(DBManager dbManager) {
        this.dbManager = dbManager;
    }

    @Override
    public Country find(int id) throws DBException {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Country country = null;
        try
        {
            preparedStatement = dbManager.prepareStatement(FIND_COUNTRY_BY_ID_SQL);
            preparedStatement.setInt(1, id);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next())
            {
                country = getCountryEntity(resultSet);
            }
        }
        catch (SQLException ex)
        {
            throw new DBException(ex.getMessage(), ex);
        }
        finally {
            closeResultSet(resultSet);
            closeStatement(preparedStatement);
        }
        return country;
    }

    @Override
    public List<Country> findAll() throws DBException {
        ResultSet resultSet = dbManager.execSQL(FIND_ALL_COUNTRY_SQL);
        List<Country> countries = new ArrayList<Country>();
        try
        {
            while(resultSet.next())
            {
                countries.add(getCountryEntity(resultSet));
            }
        }
        catch(SQLException ex)
        {
            throw new DBException(ex.getMessage(), ex);
        }
        finally
        {
            closeResultSet(resultSet);
        }
        return countries;
    }

    @Override
    public int count() throws DBException {
        ResultSet resultSet = dbManager.execSQL(FIND_COUNTRY_COUNT_SQL);
        int count = 0;
        try
        {
            while(resultSet.next())
            {
                count = resultSet.getInt(1);
            }
        }
        catch (SQLException ex)
        {
            throw new DBException(ex.getMessage(), ex);
        }
        finally
        {
            closeResultSet(resultSet);
        }
        return count;
    }

    private Country getCountryEntity(ResultSet resultSet) throws SQLException, DBException
    {
        Country country = new Country();
        country.setId(resultSet.getInt(COUNTRY_ID_VALUE));
        country.setName(resultSet.getString(COUNTRY_NAME_VALUE));
        return country;
    }
}
